<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>SQL语法基础小结 | ShuangChenYue</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="https://cdn.jsdelivr.net/gh/cmty256/imgs-blog@main/logo/白云.38zbldnhh180.jpg">
    <meta name="description" content="满招损，谦受益">
    <meta name="keywords" content="专注于Cpp语言的旅行者">
    
    <link rel="preload" href="/assets/css/0.styles.3fffbd7d.css" as="style"><link rel="preload" href="/assets/js/app.ef25b098.js" as="script"><link rel="preload" href="/assets/js/2.4615a819.js" as="script"><link rel="preload" href="/assets/js/68.163161cc.js" as="script"><link rel="prefetch" href="/assets/js/10.5712a6b9.js"><link rel="prefetch" href="/assets/js/11.1fed716c.js"><link rel="prefetch" href="/assets/js/12.4672093d.js"><link rel="prefetch" href="/assets/js/13.cda96393.js"><link rel="prefetch" href="/assets/js/14.0924c9b3.js"><link rel="prefetch" href="/assets/js/15.443e1f82.js"><link rel="prefetch" href="/assets/js/16.554bf912.js"><link rel="prefetch" href="/assets/js/17.994ca8e7.js"><link rel="prefetch" href="/assets/js/18.4879414d.js"><link rel="prefetch" href="/assets/js/19.f66c5ece.js"><link rel="prefetch" href="/assets/js/20.79449f9c.js"><link rel="prefetch" href="/assets/js/21.0b51ee17.js"><link rel="prefetch" href="/assets/js/22.14f9241e.js"><link rel="prefetch" href="/assets/js/23.fa858e5d.js"><link rel="prefetch" href="/assets/js/24.a420d3d2.js"><link rel="prefetch" href="/assets/js/25.fee08d36.js"><link rel="prefetch" href="/assets/js/26.92805480.js"><link rel="prefetch" href="/assets/js/27.5c60f91f.js"><link rel="prefetch" href="/assets/js/28.2bbf1597.js"><link rel="prefetch" href="/assets/js/29.05fbd423.js"><link rel="prefetch" href="/assets/js/3.7b56aee3.js"><link rel="prefetch" href="/assets/js/30.54f725db.js"><link rel="prefetch" href="/assets/js/31.3e5c530d.js"><link rel="prefetch" href="/assets/js/32.f8ecda14.js"><link rel="prefetch" href="/assets/js/33.3bef3da7.js"><link rel="prefetch" href="/assets/js/34.0e1b1990.js"><link rel="prefetch" href="/assets/js/35.21acae61.js"><link rel="prefetch" href="/assets/js/36.744eb2f7.js"><link rel="prefetch" href="/assets/js/37.077678f2.js"><link rel="prefetch" href="/assets/js/38.d2c19552.js"><link rel="prefetch" href="/assets/js/39.d873f04b.js"><link rel="prefetch" href="/assets/js/4.8844c7c6.js"><link rel="prefetch" href="/assets/js/40.a9c0394e.js"><link rel="prefetch" href="/assets/js/41.bd1b72f7.js"><link rel="prefetch" href="/assets/js/42.8c5ba50f.js"><link rel="prefetch" href="/assets/js/43.aac76280.js"><link rel="prefetch" href="/assets/js/44.2601ff73.js"><link rel="prefetch" href="/assets/js/45.2802dd41.js"><link rel="prefetch" href="/assets/js/46.3a872e80.js"><link rel="prefetch" href="/assets/js/47.fb914cfa.js"><link rel="prefetch" href="/assets/js/48.4ce4214b.js"><link rel="prefetch" href="/assets/js/49.968e0b60.js"><link rel="prefetch" href="/assets/js/5.d85ca603.js"><link rel="prefetch" href="/assets/js/50.4cee1e39.js"><link rel="prefetch" href="/assets/js/51.5cad3040.js"><link rel="prefetch" href="/assets/js/52.1612ad39.js"><link rel="prefetch" href="/assets/js/53.e9cf7aec.js"><link rel="prefetch" href="/assets/js/54.2821cc0e.js"><link rel="prefetch" href="/assets/js/55.6f8ddb85.js"><link rel="prefetch" href="/assets/js/56.58b793ba.js"><link rel="prefetch" href="/assets/js/57.3d08056f.js"><link rel="prefetch" href="/assets/js/58.880ee89f.js"><link rel="prefetch" href="/assets/js/59.987dab20.js"><link rel="prefetch" href="/assets/js/6.b5c87a76.js"><link rel="prefetch" href="/assets/js/60.6089e8d9.js"><link rel="prefetch" href="/assets/js/61.2f20e8a2.js"><link rel="prefetch" href="/assets/js/62.03592460.js"><link rel="prefetch" href="/assets/js/63.77002dd4.js"><link rel="prefetch" href="/assets/js/64.180619d0.js"><link rel="prefetch" href="/assets/js/65.2750f9b6.js"><link rel="prefetch" href="/assets/js/66.c696b1af.js"><link rel="prefetch" href="/assets/js/67.03363cd9.js"><link rel="prefetch" href="/assets/js/69.6d87bcc0.js"><link rel="prefetch" href="/assets/js/7.70b0614b.js"><link rel="prefetch" href="/assets/js/70.53173d63.js"><link rel="prefetch" href="/assets/js/71.5acaf78b.js"><link rel="prefetch" href="/assets/js/72.e5473318.js"><link rel="prefetch" href="/assets/js/73.fca88a39.js"><link rel="prefetch" href="/assets/js/74.d45cfae5.js"><link rel="prefetch" href="/assets/js/75.81660c22.js"><link rel="prefetch" href="/assets/js/76.3e7aeba1.js"><link rel="prefetch" href="/assets/js/77.2e4da6e0.js"><link rel="prefetch" href="/assets/js/78.7f7d7694.js"><link rel="prefetch" href="/assets/js/79.f60ae352.js"><link rel="prefetch" href="/assets/js/8.ecc019cf.js"><link rel="prefetch" href="/assets/js/80.3a10fa1d.js"><link rel="prefetch" href="/assets/js/81.72805d9a.js"><link rel="prefetch" href="/assets/js/82.c1d3f9b6.js"><link rel="prefetch" href="/assets/js/83.9014695a.js"><link rel="prefetch" href="/assets/js/84.4602494c.js"><link rel="prefetch" href="/assets/js/85.f2189c96.js"><link rel="prefetch" href="/assets/js/86.b6a21db1.js"><link rel="prefetch" href="/assets/js/87.9be7c553.js"><link rel="prefetch" href="/assets/js/88.562483e1.js"><link rel="prefetch" href="/assets/js/89.336befe6.js"><link rel="prefetch" href="/assets/js/9.4b46b767.js"><link rel="prefetch" href="/assets/js/90.e916d603.js"><link rel="prefetch" href="/assets/js/91.873db624.js"><link rel="prefetch" href="/assets/js/92.9072026b.js"><link rel="prefetch" href="/assets/js/93.0baf9593.js"><link rel="prefetch" href="/assets/js/94.8655b40d.js"><link rel="prefetch" href="/assets/js/95.bd6a4d6c.js"><link rel="prefetch" href="/assets/js/96.943e9bb3.js"><link rel="prefetch" href="/assets/js/97.ea0c9e4a.js">
    <link rel="stylesheet" href="/assets/css/0.styles.3fffbd7d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="https://cdn.jsdelivr.net/gh/cmty256/imgs-blog@main/logo/白云.38zbldnhh180.jpg" alt="ShuangChenYue" class="logo"> <span class="site-name can-hide">ShuangChenYue</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="CPP语言" class="dropdown-title"><!----> <span class="title" style="display:;">CPP语言</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/c5bdd8/" class="nav-link">Cpp之旅</a></li><li class="dropdown-item"><!----> <a href="/pages/279e62/" class="nav-link">Cpp专栏</a></li><li class="dropdown-item"><!----> <a href="/pages/801755/" class="nav-link">Effective_CPP</a></li><li class="dropdown-item"><!----> <a href="/pages/6b2468/" class="nav-link">muduo网络库</a></li><li class="dropdown-item"><!----> <a href="/pages/5f8c9f/" class="nav-link">Unix环境高级编程</a></li><li class="dropdown-item"><!----> <a href="/pages/3f1d21/" class="nav-link">Cpp提高编程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="计算机基础" class="dropdown-title"><!----> <span class="title" style="display:;">计算机基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/7b1cb2/" class="nav-link">计算机网络</a></li><li class="dropdown-item"><!----> <a href="/pages/6048a8/" class="nav-link">操作系统</a></li><li class="dropdown-item"><!----> <a href="/pages/3b34ba/" class="nav-link">数据结构</a></li><li class="dropdown-item"><!----> <a href="/pages/412fe7/" class="nav-link">Linux</a></li><li class="dropdown-item"><!----> <a href="/pages/2dcfa1/" class="nav-link">算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><!----> <span class="title" style="display:;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/efa3f2/" class="nav-link">基础篇</a></li><li class="dropdown-item"><!----> <a href="/pages/ccc445/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/pages/54616e/" class="nav-link">Redis</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="系统设计" class="dropdown-title"><!----> <span class="title" style="display:;">系统设计</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/793d0a/" class="nav-link">权限校验</a></li><li class="dropdown-item"><!----> <a href="/pages/73ddd7/" class="nav-link">设计模式</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="开发日常" class="dropdown-title"><!----> <span class="title" style="display:;">开发日常</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/71f6ae/" class="nav-link">Git知识总结</a></li><li class="dropdown-item"><!----> <a href="/pages/777b8a/" class="nav-link">nvm使用小结</a></li><li class="dropdown-item"><!----> <a href="/pages/ee770e/" class="nav-link">虚拟机固定 IP 地址</a></li><li class="dropdown-item"><!----> <a href="/pages/e472d1/" class="nav-link">随笔（持续更新）</a></li><li class="dropdown-item"><!----> <a href="/pages/411aa4/" class="nav-link">VScode 插件 CodeGeeX 使用教程</a></li><li class="dropdown-item"><!----> <a href="/pages/0d525d/" class="nav-link">KylinV10 将项目上传至 Github教程</a></li><li class="dropdown-item"><!----> <a href="/pages/ef40f0/" class="nav-link">KylinV10 安装 MySQL 教程（可防踩雷）</a></li><li class="dropdown-item"><!----> <a href="/pages/f8640c/" class="nav-link">个人博客代码推送教程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端学习" class="dropdown-title"><!----> <span class="title" style="display:;">前端学习</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/99897f/" class="nav-link">HTML与CSS</a></li><li class="dropdown-item"><!----> <a href="/pages/51542d/" class="nav-link">JS学习</a></li><li class="dropdown-item"><!----> <a href="/pages/803f9d/" class="nav-link">Vue3入门</a></li><li class="dropdown-item"><!----> <a href="/pages/ca4cfb/" class="nav-link">Vue3进阶</a></li><li class="dropdown-item"><!----> <a href="/pages/50e8d3/" class="nav-link">黑马Vue3</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="项目笔记" class="dropdown-title"><!----> <span class="title" style="display:;">项目笔记</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/08dfe9/" class="nav-link">ip_file_hook</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面经" class="dropdown-title"><!----> <span class="title" style="display:;">面经</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/d69946/" class="nav-link">虎牙C++技术面经</a></li><li class="dropdown-item"><!----> <a href="/pages/29251d/" class="nav-link">金山一面复习</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其它" class="dropdown-title"><!----> <span class="title" style="display:;">其它</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/fa256e/" class="nav-link">博客搭建</a></li><li class="dropdown-item"><!----> <a href="/pages/335531/" class="nav-link">网站收藏箱</a></li></ul></div></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="CPP语言" class="dropdown-title"><!----> <span class="title" style="display:;">CPP语言</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/c5bdd8/" class="nav-link">Cpp之旅</a></li><li class="dropdown-item"><!----> <a href="/pages/279e62/" class="nav-link">Cpp专栏</a></li><li class="dropdown-item"><!----> <a href="/pages/801755/" class="nav-link">Effective_CPP</a></li><li class="dropdown-item"><!----> <a href="/pages/6b2468/" class="nav-link">muduo网络库</a></li><li class="dropdown-item"><!----> <a href="/pages/5f8c9f/" class="nav-link">Unix环境高级编程</a></li><li class="dropdown-item"><!----> <a href="/pages/3f1d21/" class="nav-link">Cpp提高编程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="计算机基础" class="dropdown-title"><!----> <span class="title" style="display:;">计算机基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/7b1cb2/" class="nav-link">计算机网络</a></li><li class="dropdown-item"><!----> <a href="/pages/6048a8/" class="nav-link">操作系统</a></li><li class="dropdown-item"><!----> <a href="/pages/3b34ba/" class="nav-link">数据结构</a></li><li class="dropdown-item"><!----> <a href="/pages/412fe7/" class="nav-link">Linux</a></li><li class="dropdown-item"><!----> <a href="/pages/2dcfa1/" class="nav-link">算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><!----> <span class="title" style="display:;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/efa3f2/" class="nav-link">基础篇</a></li><li class="dropdown-item"><!----> <a href="/pages/ccc445/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/pages/54616e/" class="nav-link">Redis</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="系统设计" class="dropdown-title"><!----> <span class="title" style="display:;">系统设计</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/793d0a/" class="nav-link">权限校验</a></li><li class="dropdown-item"><!----> <a href="/pages/73ddd7/" class="nav-link">设计模式</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="开发日常" class="dropdown-title"><!----> <span class="title" style="display:;">开发日常</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/71f6ae/" class="nav-link">Git知识总结</a></li><li class="dropdown-item"><!----> <a href="/pages/777b8a/" class="nav-link">nvm使用小结</a></li><li class="dropdown-item"><!----> <a href="/pages/ee770e/" class="nav-link">虚拟机固定 IP 地址</a></li><li class="dropdown-item"><!----> <a href="/pages/e472d1/" class="nav-link">随笔（持续更新）</a></li><li class="dropdown-item"><!----> <a href="/pages/411aa4/" class="nav-link">VScode 插件 CodeGeeX 使用教程</a></li><li class="dropdown-item"><!----> <a href="/pages/0d525d/" class="nav-link">KylinV10 将项目上传至 Github教程</a></li><li class="dropdown-item"><!----> <a href="/pages/ef40f0/" class="nav-link">KylinV10 安装 MySQL 教程（可防踩雷）</a></li><li class="dropdown-item"><!----> <a href="/pages/f8640c/" class="nav-link">个人博客代码推送教程</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端学习" class="dropdown-title"><!----> <span class="title" style="display:;">前端学习</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/99897f/" class="nav-link">HTML与CSS</a></li><li class="dropdown-item"><!----> <a href="/pages/51542d/" class="nav-link">JS学习</a></li><li class="dropdown-item"><!----> <a href="/pages/803f9d/" class="nav-link">Vue3入门</a></li><li class="dropdown-item"><!----> <a href="/pages/ca4cfb/" class="nav-link">Vue3进阶</a></li><li class="dropdown-item"><!----> <a href="/pages/50e8d3/" class="nav-link">黑马Vue3</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="项目笔记" class="dropdown-title"><!----> <span class="title" style="display:;">项目笔记</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/08dfe9/" class="nav-link">ip_file_hook</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面经" class="dropdown-title"><!----> <span class="title" style="display:;">面经</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/d69946/" class="nav-link">虎牙C++技术面经</a></li><li class="dropdown-item"><!----> <a href="/pages/29251d/" class="nav-link">金山一面复习</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其它" class="dropdown-title"><!----> <span class="title" style="display:;">其它</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/pages/fa256e/" class="nav-link">博客搭建</a></li><li class="dropdown-item"><!----> <a href="/pages/335531/" class="nav-link">网站收藏箱</a></li></ul></div></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>基础</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/efa3f2/" class="sidebar-link">数据库基础知识小结</a></li><li><a href="/pages/54fc03/" class="sidebar-link">NoSQL基础知识小结</a></li><li><a href="/pages/126a5a/" class="sidebar-link">字符集详解</a></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><p class="sidebar-heading open"><span>SQL</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/ed0fa0/" aria-current="page" class="active sidebar-link">SQL语法基础小结</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#一、基本概念" class="sidebar-link">一、基本概念</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#数据库术语" class="sidebar-link">数据库术语</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#语法" class="sidebar-link">语法</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#语法结构" class="sidebar-link">语法结构</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#语法要点" class="sidebar-link">语法要点</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#分类" class="sidebar-link">分类</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#数据定义语言-ddl" class="sidebar-link">数据定义语言（DDL）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#数据操纵语言-dml" class="sidebar-link">数据操纵语言（DML）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#事务控制语言-tcl" class="sidebar-link">事务控制语言（TCL）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#数据控制语言-dcl" class="sidebar-link">数据控制语言（DCL）</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#二、增删改查" class="sidebar-link">二、增删改查</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#插入数据" class="sidebar-link">插入数据</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#更新数据" class="sidebar-link">更新数据</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#删除数据" class="sidebar-link">删除数据</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#查询数据" class="sidebar-link">查询数据</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#三、子查询" class="sidebar-link">三、子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#where" class="sidebar-link">WHERE</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#in-和-between" class="sidebar-link">IN 和 BETWEEN</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#and、or、not" class="sidebar-link">AND、OR、NOT</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#like" class="sidebar-link">LIKE</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#四、连接和组合" class="sidebar-link">四、连接和组合</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#连接-join" class="sidebar-link">连接（JOIN）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#内连接-inner-join" class="sidebar-link">内连接（INNER JOIN）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#自连接" class="sidebar-link">自连接</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#自然连接-natural-join" class="sidebar-link">自然连接（NATURAL JOIN）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#左连接-left-join" class="sidebar-link">左连接（LEFT JOIN）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#右连接-right-join" class="sidebar-link">右连接（RIGHT JOIN）</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#组合-union" class="sidebar-link">组合（UNION）</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#join-vs-union" class="sidebar-link">JOIN vs UNION</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#五、函数" class="sidebar-link">五、函数</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#文本处理" class="sidebar-link">文本处理</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#日期和时间处理" class="sidebar-link">日期和时间处理</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#数值处理" class="sidebar-link">数值处理</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#汇总" class="sidebar-link">汇总</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#六、排序和分组" class="sidebar-link">六、排序和分组</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#order-by" class="sidebar-link">ORDER BY</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#group-by" class="sidebar-link">GROUP BY</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#having" class="sidebar-link">HAVING</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#七、数据定义" class="sidebar-link">七、数据定义</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#数据库-database" class="sidebar-link">数据库（DATABASE）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#创建数据库" class="sidebar-link">创建数据库</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#删除数据库" class="sidebar-link">删除数据库</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#选择数据库" class="sidebar-link">选择数据库</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#数据表-table" class="sidebar-link">数据表（TABLE）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#创建数据表" class="sidebar-link">创建数据表</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#删除数据表" class="sidebar-link">删除数据表</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#修改数据表" class="sidebar-link">修改数据表</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#视图-view" class="sidebar-link">视图（VIEW）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#创建视图" class="sidebar-link">创建视图</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#删除视图" class="sidebar-link">删除视图</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#索引-index" class="sidebar-link">索引（INDEX）</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#创建索引" class="sidebar-link">创建索引</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#创建唯一索引" class="sidebar-link">创建唯一索引</a></li><li class="sidebar-sub-header level4"><a href="/pages/ed0fa0/#删除索引" class="sidebar-link">删除索引</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#约束" class="sidebar-link">约束</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#八、事务处理" class="sidebar-link">八、事务处理</a></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#九、权限控制" class="sidebar-link">九、权限控制</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#创建账户" class="sidebar-link">创建账户</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#修改账户名" class="sidebar-link">修改账户名</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#删除账户" class="sidebar-link">删除账户</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#查看权限" class="sidebar-link">查看权限</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#授予权限" class="sidebar-link">授予权限</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#删除权限" class="sidebar-link">删除权限</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#更改密码" class="sidebar-link">更改密码</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#十、存储过程" class="sidebar-link">十、存储过程</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#创建存储过程" class="sidebar-link">创建存储过程</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#使用存储过程" class="sidebar-link">使用存储过程</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#十一、游标" class="sidebar-link">十一、游标</a></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#十二、触发器" class="sidebar-link">十二、触发器</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#创建触发器" class="sidebar-link">创建触发器</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#查看触发器" class="sidebar-link">查看触发器</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#删除触发器" class="sidebar-link">删除触发器</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#实际运用" class="sidebar-link">实际运用</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#实际开发中-为什么要替换和避免-sql-中的-in-查询" class="sidebar-link">实际开发中，为什么要替换和避免 SQL 中的 IN 查询？</a></li><li class="sidebar-sub-header level3"><a href="/pages/ed0fa0/#为什么可以用-exists-子查询" class="sidebar-link">为什么可以用 EXISTS 子查询？</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/ed0fa0/#参考文献" class="sidebar-link">参考文献</a></li></ul></li><li><a href="/pages/def622/" class="sidebar-link">SQL必知必会题解</a></li></ul></section></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Mysql</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Redis</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><span data-v-06225672>数据库</span></li><li data-v-06225672><span data-v-06225672>基础</span></li><li data-v-06225672><span data-v-06225672>SQL</span></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="javascript:;" data-v-06225672>霜晨月</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-07-24</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">SQL语法基础小结<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="sql-语法基础小结"><a href="#sql-语法基础小结" class="header-anchor">#</a> SQL 语法基础小结</h1> <h2 id="一、基本概念"><a href="#一、基本概念" class="header-anchor">#</a> 一、基本概念</h2> <h3 id="数据库术语"><a href="#数据库术语" class="header-anchor">#</a> 数据库术语</h3> <ul><li><code>数据库（database）</code> - 保存有组织的数据的容器（通常是一个文件或一组文件）。</li> <li><code>数据表（table）</code> - 某种特定类型数据的结构化清单。</li> <li><code>模式（schema）</code> - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。</li> <li><code>列（column）</code> - 表中的一个字段。所有表都是由一个或多个列组成的。</li> <li><code>行（row）</code> - 表中的一个记录。</li> <li><code>主键（primary key）</code> - 一列（或一组列），其值能够唯一标识表中每一行。</li></ul> <h3 id="语法"><a href="#语法" class="header-anchor">#</a> 语法</h3> <blockquote><p>（Structured Query Language)，标准  由 ANSI 标准委员会管理，从而称为 ANSI 。各个 DBMS 都有自己的实现，如 PL/、Transact- 等。</p></blockquote> <h4 id="语法结构"><a href="#语法结构" class="header-anchor">#</a> 语法结构</h4> <p><img src="https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2020/1/15/16fa77ca4cc88a8a~tplv-t2oaga2asx-zoom-in-crop-mark:3024:0:0:0.awebp" alt="-syntax"></p> <p>语法结构包括：</p> <ul><li><strong><code>子句</code></strong> - 是语句和查询的组成成分。（在某些情况下，这些都是可选的。）</li> <li><strong><code>表达式</code></strong> - 可以产生任何标量值，或由列和行的数据库表</li> <li><strong><code>谓词</code></strong> - 给需要评估的  三值逻辑（3VL）（true/false/unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。</li> <li><strong><code>查询</code></strong> - 基于特定条件检索数据。这是  的一个重要组成部分。</li> <li><strong><code>语句</code></strong> - 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。</li></ul> <h4 id="语法要点"><a href="#语法要点" class="header-anchor">#</a> 语法要点</h4> <ul><li><strong>语句不区分大小写</strong>，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。</li></ul> <p>例如：<code>SELECT</code> 与 <code>select</code> 、<code>Select</code> 是相同的。</p> <ul><li><strong>多条  语句必须以分号（<code>;</code>）分隔</strong>。</li> <li>处理  语句时，<strong>所有空格都被忽略</strong>。 语句可以写成一行，也可以分写为多行。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 一行  语句</span>
<span class="token keyword">UPDATE</span> <span class="token keyword">user</span> <span class="token keyword">SET</span> username<span class="token operator">=</span><span class="token string">'robot'</span><span class="token punctuation">,</span> password<span class="token operator">=</span><span class="token string">'robot'</span> <span class="token keyword">WHERE</span> username <span class="token operator">=</span> <span class="token string">'root'</span><span class="token punctuation">;</span>

<span class="token comment">-- 多行  语句</span>
<span class="token keyword">UPDATE</span> <span class="token keyword">user</span>
<span class="token keyword">SET</span> username<span class="token operator">=</span><span class="token string">'robot'</span><span class="token punctuation">,</span> password<span class="token operator">=</span><span class="token string">'robot'</span>
<span class="token keyword">WHERE</span> username <span class="token operator">=</span> <span class="token string">'root'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><ul><li>支持三种注释</li></ul> <div class="language-awk line-numbers-mode"><pre class="language-awk"><code><span class="token comment">## 注释1</span>
<span class="token operator">--</span> 注释<span class="token number">2</span>
<span class="token operator">/</span><span class="token operator">*</span> 注释<span class="token number">3</span> <span class="token operator">*</span><span class="token operator">/</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="分类"><a href="#分类" class="header-anchor">#</a> 分类</h4> <h4 id="数据定义语言-ddl"><a href="#数据定义语言-ddl" class="header-anchor">#</a> 数据定义语言（DDL）</h4> <p>数据定义语言（Data Definition Language，DDL）是  语言集中负责数据结构定义与数据库对象定义的语言。</p> <p>DDL 的主要功能是<strong>定义数据库对象</strong>。</p> <p>DDL 的核心指令是 <code>CREATE</code>、<code>ALTER</code>、<code>DROP</code>。</p> <h4 id="数据操纵语言-dml"><a href="#数据操纵语言-dml" class="header-anchor">#</a> 数据操纵语言（DML）</h4> <p>数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。</p> <p>DML 的主要功能是 <strong>访问数据</strong>，因此其语法都是以<strong>读写数据库</strong>为主。</p> <p>DML 的核心指令是 <code>INSERT</code>、<code>UPDATE</code>、<code>DELETE</code>、<code>SELECT</code>。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查。</p> <h4 id="事务控制语言-tcl"><a href="#事务控制语言-tcl" class="header-anchor">#</a> 事务控制语言（TCL）</h4> <p>事务控制语言 (Transaction Control Language, TCL) 用于<strong>管理数据库中的事务</strong>。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。</p> <p>TCL 的核心指令是 <code>COMMIT</code>、<code>ROLLBACK</code>。</p> <h4 id="数据控制语言-dcl"><a href="#数据控制语言-dcl" class="header-anchor">#</a> 数据控制语言（DCL）</h4> <p>数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。</p> <p>DCL 的核心指令是 <code>GRANT</code>、<code>REVOKE</code>。</p> <p>DCL 以<strong>控制用户的访问权限</strong>为主，因此其指令作法并不复杂，可利用 DCL 控制的权限有：<code>CONNECT</code>、<code>SELECT</code>、<code>INSERT</code>、<code>UPDATE</code>、<code>DELETE</code>、<code>EXECUTE</code>、<code>USAGE</code>、<code>REFERENCES</code>。</p> <p>根据不同的 DBMS 以及不同的安全性实体，其支持的权限控制也有所不同。</p> <hr> <p><em>（以下为 DML 语句用法）</em></p> <h2 id="二、增删改查"><a href="#二、增删改查" class="header-anchor">#</a> 二、增删改查</h2> <blockquote><p>增删改查，又称为 CRUD，数据库基本操作中的基本操作。</p></blockquote> <h3 id="插入数据"><a href="#插入数据" class="header-anchor">#</a> 插入数据</h3> <blockquote><ul><li><code>INSERT INTO</code> 语句用于向表中插入新记录。</li></ul></blockquote> <p><strong>插入完整的行</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token keyword">user</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token string">'root'</span><span class="token punctuation">,</span> <span class="token string">'root'</span><span class="token punctuation">,</span> <span class="token string">'xxxx@163.com'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>插入行的一部分</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token keyword">user</span><span class="token punctuation">(</span>username<span class="token punctuation">,</span> password<span class="token punctuation">,</span> email<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'admin'</span><span class="token punctuation">,</span> <span class="token string">'admin'</span><span class="token punctuation">,</span> <span class="token string">'xxxx@163.com'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>插入查询出来的数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token keyword">user</span><span class="token punctuation">(</span>username<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> name
<span class="token keyword">FROM</span> account<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="更新数据"><a href="#更新数据" class="header-anchor">#</a> 更新数据</h3> <blockquote><ul><li><code>UPDATE</code> 语句用于更新表中的记录。</li></ul></blockquote> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> <span class="token keyword">user</span>
<span class="token keyword">SET</span> username<span class="token operator">=</span><span class="token string">'robot'</span><span class="token punctuation">,</span> password<span class="token operator">=</span><span class="token string">'robot'</span>
<span class="token keyword">WHERE</span> username <span class="token operator">=</span> <span class="token string">'root'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="删除数据"><a href="#删除数据" class="header-anchor">#</a> 删除数据</h3> <blockquote><ul><li><code>DELETE</code> 语句用于删除表中的记录。</li> <li><code>TRUNCATE TABLE</code> 可以清空表，也就是删除所有行。</li></ul></blockquote> <p><strong>删除表中的指定数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> <span class="token keyword">user</span>
<span class="token keyword">WHERE</span> username <span class="token operator">=</span> <span class="token string">'robot'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>清空表中的数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">TRUNCATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="查询数据"><a href="#查询数据" class="header-anchor">#</a> 查询数据</h3> <blockquote><ul><li><p><code>SELECT</code> 语句用于从数据库中查询数据。</p></li> <li><p><code>DISTINCT</code> 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。</p></li> <li><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">LIMIT</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。</p> <ul><li><code>ASC</code> ：升序（默认）</li> <li><code>DESC</code> ：降序</li></ul></li></ul></blockquote> <p><strong>查询单列</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_name
<span class="token keyword">FROM</span> products<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>查询多列</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_id<span class="token punctuation">,</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> products<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>查询所有列</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> products<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>查询不同的值</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span>
vend_id <span class="token keyword">FROM</span> products<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>限制查询结果</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 返回前 5 行</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token comment">-- 返回第 3 ~ 5 行</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">LIMIT</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h2 id="三、子查询"><a href="#三、子查询" class="header-anchor">#</a> 三、子查询</h2> <blockquote><p>子查询是嵌套在较大查询中的  查询。子查询也称为<strong>内部查询</strong>或<strong>内部选择</strong>，而包含子查询的语句也称为<strong>外部查询</strong>或<strong>外部选择</strong>。</p></blockquote> <ul><li><p>子查询可以嵌套在 <code>SELECT</code>，<code>INSERT</code>，<code>UPDATE</code> 或 <code>DELETE</code> 语句内或另一个子查询中。</p></li> <li><p>子查询通常会在另一个 <code>SELECT</code> 语句的 <code>WHERE</code> 子句中添加。</p></li> <li><p>您可以使用比较运算符，如 <code>&gt;</code>，<code>&lt;</code>，或 <code>=</code>。比较运算符也可以是多行运算符，如 <code>IN</code>，<code>ANY</code> 或 <code>ALL</code>。</p></li> <li><p>子查询必须被圆括号 <code>()</code> 括起来。</p></li> <li><p>内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。执行过程可以参考下图：</p> <p><img src="https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2020/1/15/16fa77ca4da1bb77~tplv-t2oaga2asx-zoom-in-crop-mark:3024:0:0:0.awebp" alt="-subqueries"></p></li></ul> <p><strong>子查询的子查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
    cust_name<span class="token punctuation">,</span>
    cust_contact
<span class="token keyword">FROM</span>
    customers
<span class="token keyword">WHERE</span>
    cust_id <span class="token operator">IN</span> <span class="token punctuation">(</span>
        <span class="token keyword">SELECT</span>
            cust_id
        <span class="token keyword">FROM</span>
            orders
        <span class="token keyword">WHERE</span>
            order_num <span class="token operator">IN</span> <span class="token punctuation">(</span>
                <span class="token keyword">SELECT</span>
                    order_num
                <span class="token keyword">FROM</span>
                    orderitems
                <span class="token keyword">WHERE</span>
                    prod_id <span class="token operator">=</span> <span class="token string">'RGAN01'</span>
            <span class="token punctuation">)</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br></div></div><h3 id="where"><a href="#where" class="header-anchor">#</a> WHERE</h3> <ul><li><code>WHERE</code> 子句用于过滤记录，即缩小访问数据的范围。</li> <li><code>WHERE</code> 后跟一个返回 <code>true</code> 或 <code>false</code> 的条件。</li> <li><code>WHERE</code> 可以与 <code>SELECT</code>，<code>UPDATE</code> 和 <code>DELETE</code> 一起使用。</li> <li>可以在 <code>WHERE</code> 子句中使用的操作符</li></ul> <table><thead><tr><th>运算符</th> <th>描述</th></tr></thead> <tbody><tr><td>=</td> <td>等于</td></tr> <tr><td>&lt;&gt;</td> <td>不等于。注释：在  的一些版本中，该操作符可被写成 !=</td></tr> <tr><td>&gt;</td> <td>大于</td></tr> <tr><td>&lt;</td> <td>小于</td></tr> <tr><td>&gt;=</td> <td>大于等于</td></tr> <tr><td>&lt;=</td> <td>小于等于</td></tr> <tr><td>BETWEEN</td> <td>在某个范围内</td></tr> <tr><td>LIKE</td> <td>搜索某种模式</td></tr> <tr><td>IN</td> <td>指定针对某个列的多个可能值</td></tr></tbody></table> <p><strong><code>SELECT</code> 语句中的 <code>WHERE</code> 子句</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Customers
<span class="token keyword">WHERE</span> cust_name <span class="token operator">=</span> <span class="token string">'Kids Place'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong><code>UPDATE</code> 语句中的 <code>WHERE</code> 子句</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> Customers
<span class="token keyword">SET</span> cust_name <span class="token operator">=</span> <span class="token string">'Jack Jones'</span>
<span class="token keyword">WHERE</span> cust_name <span class="token operator">=</span> <span class="token string">'Kids Place'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong><code>DELETE</code> 语句中的 <code>WHERE</code> 子句</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> Customers
<span class="token keyword">WHERE</span> cust_name <span class="token operator">=</span> <span class="token string">'Kids Place'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="in-和-between"><a href="#in-和-between" class="header-anchor">#</a> IN 和 BETWEEN</h3> <ul><li><code>IN</code> 操作符在 <code>WHERE</code> 子句中使用，作用是在指定的几个特定值中任选一个值。</li> <li><code>BETWEEN</code> 操作符在 <code>WHERE</code> 子句中使用，作用是选取介于某个范围内的值。</li></ul> <p><strong>IN 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> vend_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'DLL01'</span><span class="token punctuation">,</span> <span class="token string">'BRS01'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>BETWEEN 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> prod_price <span class="token operator">BETWEEN</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token number">5</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="and、or、not"><a href="#and、or、not" class="header-anchor">#</a> AND、OR、NOT</h3> <ul><li><code>AND</code>、<code>OR</code>、<code>NOT</code> 是用于对过滤条件的逻辑处理指令。</li> <li><code>AND</code> 优先级高于 <code>OR</code>，为了明确处理顺序，可以使用 <code>()</code>。</li> <li><code>AND</code> 操作符表示左右条件都要满足。</li> <li><code>OR</code> 操作符表示左右条件满足任意一个即可。</li> <li><code>NOT</code> 操作符用于否定一个条件。</li></ul> <p><strong>AND 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_id<span class="token punctuation">,</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> vend_id <span class="token operator">=</span> <span class="token string">'DLL01'</span> <span class="token operator">AND</span> prod_price <span class="token operator">&lt;=</span> <span class="token number">4</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>OR 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_id<span class="token punctuation">,</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> vend_id <span class="token operator">=</span> <span class="token string">'DLL01'</span> <span class="token operator">OR</span> vend_id <span class="token operator">=</span> <span class="token string">'BRS01'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>NOT 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> prod_price <span class="token operator">NOT</span> <span class="token operator">BETWEEN</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token number">5</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="like"><a href="#like" class="header-anchor">#</a> LIKE</h3> <ul><li><code>LIKE</code> 操作符在 <code>WHERE</code> 子句中使用，作用是确定字符串是否匹配模式。</li> <li>只有字段是文本值时才使用 <code>LIKE</code>。</li> <li><code>LIKE</code> 支持两个通配符匹配选项：<code>%</code> 和 <code>_</code>。</li> <li>不要滥用通配符，通配符位于开头处匹配会非常慢。</li> <li><code>%</code> 表示任何字符出现任意次数。</li> <li><code>_</code> 表示任何字符出现一次。</li></ul> <p><strong>% 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_id<span class="token punctuation">,</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> prod_name <span class="token operator">LIKE</span> <span class="token string">'%bean bag%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>_ 示例</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> prod_id<span class="token punctuation">,</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> products
<span class="token keyword">WHERE</span> prod_name <span class="token operator">LIKE</span> <span class="token string">'__ inch teddy bear'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="四、连接和组合"><a href="#四、连接和组合" class="header-anchor">#</a> 四、连接和组合</h2> <h3 id="连接-join"><a href="#连接-join" class="header-anchor">#</a> 连接（JOIN）</h3> <blockquote><ul><li><p>如果一个 <code>JOIN</code> 至少有一个公共字段并且它们之间存在关系，则该 <code>JOIN</code> 可以在两个或多个表上工作。</p></li> <li><p>连接用于连接多个表，使用 <code>JOIN</code> 关键字，并且条件语句使用 <code>ON</code> 而不是 <code>WHERE</code>。</p></li> <li><p><code>JOIN</code> 保持基表（结构和数据）不变。</p></li> <li><p><code>JOIN</code> 有两种连接类型：内连接和外连接。</p></li> <li><p>内连接又称等值连接，使用 INNER</p> <div class="language- line-numbers-mode"><pre class="language-text"><code>JOIN
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>关键字。在没有条件语句的情况下返回笛卡尔积。</p> <ul><li>自连接可以看成内连接的一种，只是连接的表是自身而已。</li></ul></li> <li><p>自然连接是把同名列通过 = 测试连接起来的，同名列可以有多个。</p></li> <li><p>内连接 vs 自然连接</p> <ul><li>内连接提供连接的列，而自然连接自动连接所有同名列。</li></ul></li> <li><p>外连接返回一个表中的所有行，并且仅返回来自次表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（My 不支持）。</p> <ul><li>左外连接就是保留左表没有关联的行。</li> <li>右外连接就是保留右表没有关联的行。</li></ul></li> <li><p>连接 vs 子查询</p> <ul><li>连接可以替换子查询，并且比子查询的效率一般会更快。</li></ul> <p><img src="https://p1-jj.byteimg.com/tos-cn-i-t2oaga2asx/gold-user-assets/2020/1/15/16fa77ca4ecc58cf~tplv-t2oaga2asx-zoom-in-crop-mark:3024:0:0:0.awebp" alt="-join"></p></li></ul></blockquote> <h4 id="内连接-inner-join"><a href="#内连接-inner-join" class="header-anchor">#</a> 内连接（INNER JOIN）</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> vend_name<span class="token punctuation">,</span> prod_name<span class="token punctuation">,</span> prod_price
<span class="token keyword">FROM</span> vendors <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> products
<span class="token keyword">ON</span> vendors<span class="token punctuation">.</span>vend_id <span class="token operator">=</span> products<span class="token punctuation">.</span>vend_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="自连接"><a href="#自连接" class="header-anchor">#</a> 自连接</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> c1<span class="token punctuation">.</span>cust_id<span class="token punctuation">,</span> c1<span class="token punctuation">.</span>cust_name<span class="token punctuation">,</span> c1<span class="token punctuation">.</span>cust_contact
<span class="token keyword">FROM</span> customers c1<span class="token punctuation">,</span> customers c2
<span class="token keyword">WHERE</span> c1<span class="token punctuation">.</span>cust_name <span class="token operator">=</span> c2<span class="token punctuation">.</span>cust_name
<span class="token operator">AND</span> c2<span class="token punctuation">.</span>cust_contact <span class="token operator">=</span> <span class="token string">'Jim Jones'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h4 id="自然连接-natural-join"><a href="#自然连接-natural-join" class="header-anchor">#</a> 自然连接（NATURAL JOIN）</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> Products
<span class="token keyword">NATURAL</span> <span class="token keyword">JOIN</span> Customers<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="左连接-left-join"><a href="#左连接-left-join" class="header-anchor">#</a> 左连接（LEFT JOIN）</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> customers<span class="token punctuation">.</span>cust_id<span class="token punctuation">,</span> orders<span class="token punctuation">.</span>order_num
<span class="token keyword">FROM</span> customers <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> orders
<span class="token keyword">ON</span> customers<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> orders<span class="token punctuation">.</span>cust_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="右连接-right-join"><a href="#右连接-right-join" class="header-anchor">#</a> 右连接（RIGHT JOIN）</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> customers<span class="token punctuation">.</span>cust_id<span class="token punctuation">,</span> orders<span class="token punctuation">.</span>order_num
<span class="token keyword">FROM</span> customers <span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> orders
<span class="token keyword">ON</span> customers<span class="token punctuation">.</span>cust_id <span class="token operator">=</span> orders<span class="token punctuation">.</span>cust_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="组合-union"><a href="#组合-union" class="header-anchor">#</a> 组合（UNION）</h3> <ul><li><p><code>UNION</code> 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 <code>UNION</code> 中参与查询的提取行。</p></li> <li><div class="language- line-numbers-mode"><pre class="language-text"><code>UNION
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>基本规则</p> <ul><li>所有查询的列数和列顺序必须相同。</li> <li>每个查询中涉及表的列的数据类型必须相同或兼容。</li> <li>通常返回的列名取自第一个查询。</li></ul></li> <li><p>默认会去除相同行，如果需要保留相同行，使用 <code>UNION ALL</code>。</p></li> <li><p>只能包含一个 <code>ORDER BY</code> 子句，并且必须位于语句的最后。</p></li> <li><p>应用场景</p> <ul><li>在一个查询中从不同的表返回结构数据。</li> <li>对一个表执行多个查询，按一个查询返回数据。</li></ul></li></ul> <p><strong>组合查询</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> cust_contact<span class="token punctuation">,</span> cust_email
<span class="token keyword">FROM</span> customers
<span class="token keyword">WHERE</span> cust_state <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'IL'</span><span class="token punctuation">,</span> <span class="token string">'IN'</span><span class="token punctuation">,</span> <span class="token string">'MI'</span><span class="token punctuation">)</span>
<span class="token keyword">UNION</span>
<span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> cust_contact<span class="token punctuation">,</span> cust_email
<span class="token keyword">FROM</span> customers
<span class="token keyword">WHERE</span> cust_name <span class="token operator">=</span> <span class="token string">'Fun4All'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="join-vs-union"><a href="#join-vs-union" class="header-anchor">#</a> JOIN vs UNION</h3> <ul><li>JOIN vs UNION
<ul><li><code>JOIN</code> 中连接表的列可能不同，但在 <code>UNION</code> 中，所有查询的列数和列顺序必须相同。</li> <li><code>UNION</code> 将查询之后的行放在一起（垂直放置），但 <code>JOIN</code> 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。</li></ul></li></ul> <h2 id="五、函数"><a href="#五、函数" class="header-anchor">#</a> 五、函数</h2> <blockquote><p>🔔 注意：不同数据库的函数往往各不相同，因此不可移植。本节主要以 My 的函数为例。</p></blockquote> <h3 id="文本处理"><a href="#文本处理" class="header-anchor">#</a> 文本处理</h3> <table><thead><tr><th>函数</th> <th>说明</th></tr></thead> <tbody><tr><td><code>LEFT()</code>、<code>RIGHT()</code></td> <td>左边或者右边的字符</td></tr> <tr><td><code>LOWER()</code>、<code>UPPER()</code></td> <td>转换为小写或者大写</td></tr> <tr><td><code>LTRIM()</code>、<code>RTIM()</code></td> <td>去除左边或者右边的空格</td></tr> <tr><td><code>LENGTH()</code></td> <td>长度</td></tr> <tr><td><code>SOUNDEX()</code></td> <td>转换为语音值</td></tr></tbody></table> <p>其中，<strong>SOUNDEX()</strong> 可以将一个字符串转换为描述其语音表示的字母数字模式。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> mytable
<span class="token keyword">WHERE</span> SOUNDEX<span class="token punctuation">(</span>col1<span class="token punctuation">)</span> <span class="token operator">=</span> SOUNDEX<span class="token punctuation">(</span><span class="token string">'apple'</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="日期和时间处理"><a href="#日期和时间处理" class="header-anchor">#</a> 日期和时间处理</h3> <ul><li>日期格式：<code>YYYY-MM-DD</code></li> <li>时间格式：<code>HH:MM:SS</code></li></ul> <table><thead><tr><th>函 数</th> <th>说 明</th></tr></thead> <tbody><tr><td><code>AddDate()</code></td> <td>增加一个日期（天、周等）</td></tr> <tr><td><code>AddTime()</code></td> <td>增加一个时间（时、分等）</td></tr> <tr><td><code>CurDate()</code></td> <td>返回当前日期</td></tr> <tr><td><code>CurTime()</code></td> <td>返回当前时间</td></tr> <tr><td><code>Date()</code></td> <td>返回日期时间的日期部分</td></tr> <tr><td><code>DateDiff()</code></td> <td>计算两个日期之差</td></tr> <tr><td><code>Date_Add()</code></td> <td>高度灵活的日期运算函数</td></tr> <tr><td><code>Date_Format()</code></td> <td>返回一个格式化的日期或时间串</td></tr> <tr><td><code>Day()</code></td> <td>返回一个日期的天数部分</td></tr> <tr><td><code>DayOfWeek()</code></td> <td>对于一个日期，返回对应的星期几</td></tr> <tr><td><code>Hour()</code></td> <td>返回一个时间的小时部分</td></tr> <tr><td><code>Minute()</code></td> <td>返回一个时间的分钟部分</td></tr> <tr><td><code>Month()</code></td> <td>返回一个日期的月份部分</td></tr> <tr><td><code>Now()</code></td> <td>返回当前日期和时间</td></tr> <tr><td><code>Second()</code></td> <td>返回一个时间的秒部分</td></tr> <tr><td><code>Time()</code></td> <td>返回一个日期时间的时间部分</td></tr> <tr><td><code>Year()</code></td> <td>返回一个日期的年份部分</td></tr></tbody></table> <div class="language-csharp line-numbers-mode"><pre class="language-csharp"><code>my<span class="token operator">&gt;</span> <span class="token return-type class-name">SELECT</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><div class="language-apache line-numbers-mode"><pre class="language-text"><code>2018-4-14 20:25:11
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="数值处理"><a href="#数值处理" class="header-anchor">#</a> 数值处理</h3> <table><thead><tr><th>函数</th> <th>说明</th></tr></thead> <tbody><tr><td>SIN()</td> <td>正弦</td></tr> <tr><td>COS()</td> <td>余弦</td></tr> <tr><td>TAN()</td> <td>正切</td></tr> <tr><td>ABS()</td> <td>绝对值</td></tr> <tr><td>SQRT()</td> <td>平方根</td></tr> <tr><td>MOD()</td> <td>余数</td></tr> <tr><td>EXP()</td> <td>指数</td></tr> <tr><td>PI()</td> <td>圆周率</td></tr> <tr><td>RAND()</td> <td>随机数</td></tr></tbody></table> <h3 id="汇总"><a href="#汇总" class="header-anchor">#</a> 汇总</h3> <table><thead><tr><th>函 数</th> <th>说 明</th></tr></thead> <tbody><tr><td><code>AVG()</code></td> <td>返回某列的平均值</td></tr> <tr><td><code>COUNT()</code></td> <td>返回某列的行数</td></tr> <tr><td><code>MAX()</code></td> <td>返回某列的最大值</td></tr> <tr><td><code>MIN()</code></td> <td>返回某列的最小值</td></tr> <tr><td><code>SUM()</code></td> <td>返回某列值之和</td></tr></tbody></table> <p><code>AVG()</code> 会忽略 NULL 行。</p> <p>使用 DISTINCT 可以让汇总函数值汇总不同的值。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> col1<span class="token punctuation">)</span> <span class="token keyword">AS</span> avg_col
<span class="token keyword">FROM</span> mytable
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="六、排序和分组"><a href="#六、排序和分组" class="header-anchor">#</a> 六、排序和分组</h2> <h3 id="order-by"><a href="#order-by" class="header-anchor">#</a> ORDER BY</h3> <ul><li><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>用于对结果集进行排序。</p> <ul><li><code>ASC</code> ：升序（默认）</li> <li><code>DESC</code> ：降序</li></ul></li> <li><p>可以按多个列进行排序，并且为每个列指定不同的排序方式</p></li></ul> <p><strong>指定多个列的排序方向</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> products
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> prod_price <span class="token keyword">DESC</span><span class="token punctuation">,</span> prod_name <span class="token keyword">ASC</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="group-by"><a href="#group-by" class="header-anchor">#</a> GROUP BY</h3> <ul><li><code>GROUP BY</code> 子句将记录分组到汇总行中。</li> <li><code>GROUP BY</code> 为每个组返回一个记录。</li> <li><code>GROUP BY</code> 通常还涉及聚合：COUNT，MAX，SUM，AVG 等。</li> <li><code>GROUP BY</code> 可以按一列或多列进行分组。</li> <li><code>GROUP BY</code> 按分组字段进行排序后，<code>ORDER BY</code> 可以以汇总字段来进行排序。</li></ul> <p><strong>分组</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>cust_address<span class="token punctuation">)</span> <span class="token keyword">AS</span> addr_num
<span class="token keyword">FROM</span> Customers <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cust_name<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>分组后排序</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>cust_address<span class="token punctuation">)</span> <span class="token keyword">AS</span> addr_num
<span class="token keyword">FROM</span> Customers <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cust_name
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> cust_name <span class="token keyword">DESC</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="having"><a href="#having" class="header-anchor">#</a> HAVING</h3> <ul><li><p><code>HAVING</code> 用于对汇总的 <code>GROUP BY</code> 结果进行过滤。</p></li> <li><p><code>HAVING</code> 要求存在一个 <code>GROUP BY</code> 子句。</p></li> <li><p><code>WHERE</code> 和 <code>HAVING</code> 可以在相同的查询中。</p></li> <li><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">HAVING</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>vs</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">WHERE</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li><code>WHERE</code> 和 <code>HAVING</code> 都是用于过滤。</li> <li><code>HAVING</code> 适用于汇总的组记录；而 WHERE 适用于单个记录。</li></ul></li></ul> <p><strong>使用 WHERE 和 HAVING 过滤数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> cust_name<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> num
<span class="token keyword">FROM</span> Customers
<span class="token keyword">WHERE</span> cust_email <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> cust_name
<span class="token keyword">HAVING</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">1</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><hr> <p><em>（以下为 DDL 语句用法）</em></p> <h2 id="七、数据定义"><a href="#七、数据定义" class="header-anchor">#</a> 七、数据定义</h2> <blockquote><p>DDL 的主要功能是定义数据库对象（如：数据库、数据表、视图、索引等）。</p></blockquote> <h3 id="数据库-database"><a href="#数据库-database" class="header-anchor">#</a> 数据库（DATABASE）</h3> <h4 id="创建数据库"><a href="#创建数据库" class="header-anchor">#</a> 创建数据库</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> test<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h4 id="删除数据库"><a href="#删除数据库" class="header-anchor">#</a> 删除数据库</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">DATABASE</span> test<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h4 id="选择数据库"><a href="#选择数据库" class="header-anchor">#</a> 选择数据库</h4> <div class="language-stata line-numbers-mode"><pre class="language-stata"><code><span class="token command keyword">USE</span> test;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="数据表-table"><a href="#数据表-table" class="header-anchor">#</a> 数据表（TABLE）</h3> <h4 id="创建数据表"><a href="#创建数据表" class="header-anchor">#</a> 创建数据表</h4> <p><strong>普通创建</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span> <span class="token punctuation">(</span>
  id <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">unsigned</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'Id'</span><span class="token punctuation">,</span>
  username <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'default'</span> <span class="token keyword">COMMENT</span> <span class="token string">'用户名'</span><span class="token punctuation">,</span>
  password <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'default'</span> <span class="token keyword">COMMENT</span> <span class="token string">'密码'</span><span class="token punctuation">,</span>
  email <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'default'</span> <span class="token keyword">COMMENT</span> <span class="token string">'邮箱'</span>
<span class="token punctuation">)</span> <span class="token keyword">COMMENT</span><span class="token operator">=</span><span class="token string">'用户表'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><strong>根据已有的表创建新表</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> vip_user <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">user</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h4 id="删除数据表"><a href="#删除数据表" class="header-anchor">#</a> 删除数据表</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h4 id="修改数据表"><a href="#修改数据表" class="header-anchor">#</a> 修改数据表</h4> <p><strong>添加列</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span>
<span class="token keyword">ADD</span> age <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>删除列</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span>
<span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> age<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>修改列</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token identifier"><span class="token punctuation">`</span>user<span class="token punctuation">`</span></span>
<span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> age <span class="token keyword">tinyint</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>添加主键</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span>
<span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>删除主键</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span>
<span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="视图-view"><a href="#视图-view" class="header-anchor">#</a> 视图（VIEW）</h3> <blockquote><ul><li>定义
<ul><li>视图是基于  语句的结果集的可视化的表。</li> <li>视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。</li></ul></li> <li>作用
<ul><li>简化复杂的  操作，比如复杂的联结；</li> <li>只使用实际表的一部分数据；</li> <li>通过只给用户访问视图的权限，保证数据的安全性；</li> <li>更改数据格式和表示。</li></ul></li></ul></blockquote> <h4 id="创建视图"><a href="#创建视图" class="header-anchor">#</a> 创建视图</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> top_10_user_view <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> username
<span class="token keyword">FROM</span> <span class="token keyword">user</span>
<span class="token keyword">WHERE</span> id <span class="token operator">&lt;</span> <span class="token number">10</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h4 id="删除视图"><a href="#删除视图" class="header-anchor">#</a> 删除视图</h4> <div class="language-gauss line-numbers-mode"><pre class="language-text"><code>DROP VIEW top_10_user_view;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="索引-index"><a href="#索引-index" class="header-anchor">#</a> 索引（INDEX）</h3> <blockquote><ul><li>作用
<ul><li>通过索引可以更加快速高效地查询数据。</li> <li>用户无法看到索引，它们只能被用来加速查询。</li></ul></li> <li>注意
<ul><li>更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。</li></ul></li> <li>唯一索引
<ul><li>唯一索引表明此索引的每一个索引值只对应唯一的数据记录。</li></ul></li></ul></blockquote> <h4 id="创建索引"><a href="#创建索引" class="header-anchor">#</a> 创建索引</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> user_index
<span class="token keyword">ON</span> <span class="token keyword">user</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h4 id="创建唯一索引"><a href="#创建唯一索引" class="header-anchor">#</a> 创建唯一索引</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> user_index
<span class="token keyword">ON</span> <span class="token keyword">user</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h4 id="删除索引"><a href="#删除索引" class="header-anchor">#</a> 删除索引</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword">user</span>
<span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> user_index<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="约束"><a href="#约束" class="header-anchor">#</a> 约束</h3> <blockquote><p>约束用于规定表中的数据规则。</p></blockquote> <ul><li>如果存在违反约束的数据行为，行为会被约束终止。</li> <li>约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。</li> <li>约束类型
<ul><li><code>NOT NULL</code> - 指示某列不能存储 NULL 值。</li> <li><code>UNIQUE</code> - 保证某列的每行必须有唯一的值。</li> <li><code>PRIMARY KEY</code> - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。</li> <li><code>FOREIGN KEY</code> - 保证一个表中的数据匹配另一个表中的值的参照完整性。</li> <li><code>CHECK</code> - 保证列中的值符合指定的条件。</li> <li><code>DEFAULT</code> - 规定没有给列赋值时的默认值。</li></ul></li></ul> <p>创建表时使用约束条件：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> Users <span class="token punctuation">(</span>
  Id <span class="token keyword">INT</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'自增Id'</span><span class="token punctuation">,</span>
  Username <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">DEFAULT</span> <span class="token string">'default'</span> <span class="token keyword">COMMENT</span> <span class="token string">'用户名'</span><span class="token punctuation">,</span>
  Password <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'default'</span> <span class="token keyword">COMMENT</span> <span class="token string">'密码'</span><span class="token punctuation">,</span>
  Email <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'default'</span> <span class="token keyword">COMMENT</span> <span class="token string">'邮箱地址'</span><span class="token punctuation">,</span>
  Enabled <span class="token keyword">TINYINT</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'是否有效'</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>Id<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">2</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4 <span class="token keyword">COMMENT</span><span class="token operator">=</span><span class="token string">'用户表'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><hr> <p><em>（以下为 TCL 语句用法）</em></p> <h2 id="八、事务处理"><a href="#八、事务处理" class="header-anchor">#</a> 八、事务处理</h2> <blockquote><ul><li>不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。</li> <li><strong>My 默认是隐式提交</strong>，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 <code>START TRANSACTION</code> 语句时，会关闭隐式提交；当 <code>COMMIT</code> 或 <code>ROLLBACK</code> 语句执行后，事务会自动关闭，重新恢复隐式提交。</li> <li>通过 <code>set autocommit=0</code> 可以取消自动提交，直到 <code>set autocommit=1</code> 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。</li> <li>指令
<ul><li><code>START TRANSACTION</code> - 指令用于标记事务的起始点。</li> <li><code>SAVEPOINT</code> - 指令用于创建保留点。</li> <li><code>ROLLBACK TO</code> - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 <code>START TRANSACTION</code> 语句处。</li> <li><code>COMMIT</code> - 提交事务。</li></ul></li></ul></blockquote> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 开始事务</span>
<span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>

<span class="token comment">-- 插入操作 A</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>user<span class="token punctuation">`</span></span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'root1'</span><span class="token punctuation">,</span> <span class="token string">'root1'</span><span class="token punctuation">,</span> <span class="token string">'xxxx@163.com'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 创建保留点 updateA</span>
<span class="token keyword">SAVEPOINT</span> updateA<span class="token punctuation">;</span>

<span class="token comment">-- 插入操作 B</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>user<span class="token punctuation">`</span></span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'root2'</span><span class="token punctuation">,</span> <span class="token string">'root2'</span><span class="token punctuation">,</span> <span class="token string">'xxxx@163.com'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 回滚到保留点 updateA</span>
<span class="token keyword">ROLLBACK</span> <span class="token keyword">TO</span> updateA<span class="token punctuation">;</span>

<span class="token comment">-- 提交事务，只有操作 A 生效</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br></div></div><hr> <p><em>（以下为 DCL 语句用法）</em></p> <h2 id="九、权限控制"><a href="#九、权限控制" class="header-anchor">#</a> 九、权限控制</h2> <blockquote><ul><li><p>GRANT 和 REVOKE 可在几个层次上控制访问权限：</p> <ul><li>整个服务器，使用 GRANT ALL 和 REVOKE ALL；</li> <li>整个数据库，使用 ON database.*；</li> <li>特定的表，使用 ON database.table；</li> <li>特定的列；</li> <li>特定的存储过程。</li></ul></li> <li><p>新创建的账户没有任何权限。</p></li> <li><p>账户用 username@host 的形式定义，username@% 使用的是默认主机名。</p></li> <li><p>My 的账户信息保存在 my 这个数据库中。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">USE</span> my<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">user</span> <span class="token keyword">FROM</span> <span class="token keyword">user</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div></li></ul></blockquote> <h3 id="创建账户"><a href="#创建账户" class="header-anchor">#</a> 创建账户</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">USER</span> myuser IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'mypassword'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="修改账户名"><a href="#修改账户名" class="header-anchor">#</a> 修改账户名</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> <span class="token keyword">user</span> <span class="token keyword">SET</span> <span class="token keyword">user</span><span class="token operator">=</span><span class="token string">'newuser'</span> <span class="token keyword">WHERE</span> <span class="token keyword">user</span><span class="token operator">=</span><span class="token string">'myuser'</span><span class="token punctuation">;</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="删除账户"><a href="#删除账户" class="header-anchor">#</a> 删除账户</h3> <div class="language-crmsh line-numbers-mode"><pre class="language-text"><code>DROP USER myuser;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="查看权限"><a href="#查看权限" class="header-anchor">#</a> 查看权限</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> GRANTS <span class="token keyword">FOR</span> myuser<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="授予权限"><a href="#授予权限" class="header-anchor">#</a> 授予权限</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> myuser<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="删除权限"><a href="#删除权限" class="header-anchor">#</a> 删除权限</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">REVOKE</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> myuser<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="更改密码"><a href="#更改密码" class="header-anchor">#</a> 更改密码</h3> <div class="language-ebnf line-numbers-mode"><pre class="language-ebnf"><code><span class="token definition rule keyword">SET PASSWORD FOR myuser</span> <span class="token operator">=</span> <span class="token string">'mypass'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="十、存储过程"><a href="#十、存储过程" class="header-anchor">#</a> 十、存储过程</h2> <blockquote><ul><li>存储过程可以看成是对一系列  操作的批处理；</li> <li>使用存储过程的好处
<ul><li>代码封装，保证了一定的安全性；</li> <li>代码复用；</li> <li>由于是预先编译，因此具有很高的性能。</li></ul></li> <li>创建存储过程
<ul><li>命令行中创建存储过程需要自定义分隔符，因为命令行是以 <code>;</code> 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。</li> <li>包含 in、out 和 inout 三种参数。</li> <li>给变量赋值都需要用 select into 语句。</li> <li>每次只能给一个变量赋值，不支持集合的操作。</li></ul></li></ul></blockquote> <h3 id="创建存储过程"><a href="#创建存储过程" class="header-anchor">#</a> 创建存储过程</h3> <div class="language-oxygene line-numbers-mode"><pre class="language-text"><code>DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><h3 id="使用存储过程"><a href="#使用存储过程" class="header-anchor">#</a> 使用存储过程</h3> <div class="language-nginx line-numbers-mode"><pre class="language-nginx"><code><span class="token directive"><span class="token keyword">nginxset</span> @b=5</span><span class="token punctuation">;</span>
<span class="token directive"><span class="token keyword">call</span> proc_adder(2,@b,@s)</span><span class="token punctuation">;</span>
<span class="token directive"><span class="token keyword">select</span> @s as sum</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="十一、游标"><a href="#十一、游标" class="header-anchor">#</a> 十一、游标</h2> <blockquote><ul><li>游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。</li> <li>在存储过程中使用游标可以对一个结果集进行移动遍历。</li> <li>游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。</li> <li>使用游标的四个步骤：
<ul><li>声明游标，这个过程没有实际检索出数据；</li> <li>打开游标；</li> <li>取出数据；</li> <li>关闭游标；</li></ul></li></ul></blockquote> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $
<span class="token keyword">CREATE</span>  <span class="token keyword">PROCEDURE</span> getTotal<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> total <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token comment">-- 创建接收游标数据的变量</span>
    <span class="token keyword">DECLARE</span> sid <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token keyword">DECLARE</span> sname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">-- 创建总数变量</span>
    <span class="token keyword">DECLARE</span> sage <span class="token keyword">INT</span><span class="token punctuation">;</span>
    <span class="token comment">-- 创建结束标志变量</span>
    <span class="token keyword">DECLARE</span> done <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">false</span><span class="token punctuation">;</span>
    <span class="token comment">-- 创建游标</span>
    <span class="token keyword">DECLARE</span> cur <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span> <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>age <span class="token keyword">from</span> cursor_table <span class="token keyword">where</span> age<span class="token operator">&gt;</span><span class="token number">30</span><span class="token punctuation">;</span>
    <span class="token comment">-- 指定游标循环结束时的返回值</span>
    <span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token operator">NOT</span> FOUND <span class="token keyword">SET</span> done <span class="token operator">=</span> <span class="token boolean">true</span><span class="token punctuation">;</span>
    <span class="token keyword">SET</span> total <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
    <span class="token keyword">OPEN</span> cur<span class="token punctuation">;</span>
    <span class="token keyword">FETCH</span> cur <span class="token keyword">INTO</span> sid<span class="token punctuation">,</span> sname<span class="token punctuation">,</span> sage<span class="token punctuation">;</span>
    <span class="token keyword">WHILE</span><span class="token punctuation">(</span><span class="token operator">NOT</span> done<span class="token punctuation">)</span>
    <span class="token keyword">DO</span>
        <span class="token keyword">SET</span> total <span class="token operator">=</span> total <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
        <span class="token keyword">FETCH</span> cur <span class="token keyword">INTO</span> sid<span class="token punctuation">,</span> sname<span class="token punctuation">,</span> sage<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>

    <span class="token keyword">CLOSE</span> cur<span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> total<span class="token punctuation">;</span>
<span class="token keyword">END</span> $
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>

<span class="token comment">-- 调用存储过程</span>
<span class="token keyword">call</span> getTotal<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br></div></div><h2 id="十二、触发器"><a href="#十二、触发器" class="header-anchor">#</a> 十二、触发器</h2> <blockquote><p>触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。</p></blockquote> <p>可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。</p> <p>My 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。</p> <p><strong><code>BEGIN</code> 和 <code>END</code></strong></p> <p>当触发器的触发条件满足时，将会执行 <code>BEGIN</code> 和 <code>END</code> 之间的触发器执行动作。</p> <blockquote><p>🔔 注意：在 My 中，分号 <code>;</code> 是语句结束的标识符，遇到分号表示该段语句已经结束，My 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。</p> <p>这时就会用到 <code>DELIMITER</code> 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：<code>DELIMITER new_delemiter</code>。<code>new_delemiter</code> 可以设为 1 个或多个长度的符号，默认的是分号 <code>;</code>，我们可以把它修改为其他符号，如 <code>$</code> - <code>DELIMITER $</code> 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 <code>$</code>，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。</p></blockquote> <p><strong><code>NEW</code> 和 <code>OLD</code></strong></p> <ul><li>My 中定义了 <code>NEW</code> 和 <code>OLD</code> 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。</li> <li>在 <code>INSERT</code> 型触发器中，<code>NEW</code> 用来表示将要（<code>BEFORE</code>）或已经（<code>AFTER</code>）插入的新数据；</li> <li>在 <code>UPDATE</code> 型触发器中，<code>OLD</code> 用来表示将要或已经被修改的原数据，<code>NEW</code> 用来表示将要或已经修改为的新数据；</li> <li>在 <code>DELETE</code> 型触发器中，<code>OLD</code> 用来表示将要或已经被删除的原数据；</li> <li>使用方法：<code>NEW.columnName</code>（columnName 为相应数据表某一列名）</li></ul> <h3 id="创建触发器"><a href="#创建触发器" class="header-anchor">#</a> 创建触发器</h3> <blockquote><p>提示：为了理解触发器的要点，有必要先了解一下创建触发器的指令。</p></blockquote> <p><code>CREATE TRIGGER</code> 指令用于创建触发器。</p> <p>语法：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> trigger_name
trigger_time
trigger_event
<span class="token keyword">ON</span> table_name
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">BEGIN</span>
  trigger_statements
<span class="token keyword">END</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p>说明：</p> <ul><li>trigger_name：触发器名</li> <li>trigger_time: 触发器的触发时机。取值为 <code>BEFORE</code> 或 <code>AFTER</code>。</li> <li>trigger_event: 触发器的监听事件。取值为 <code>INSERT</code>、<code>UPDATE</code> 或 <code>DELETE</code>。</li> <li>table_name: 触发器的监听目标。指定在哪张表上建立触发器。</li> <li>FOR EACH ROW: 行级监视，My 固定写法，其他 DBMS 不同。</li> <li>trigger_statements: 触发器执行动作。是一条或多条  语句的列表，列表内的每条语句都必须用分号 <code>;</code> 来结尾。</li></ul> <p>示例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELIMITER</span> $
<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> <span class="token identifier"><span class="token punctuation">`</span>trigger_insert_user<span class="token punctuation">`</span></span>
<span class="token keyword">AFTER</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token identifier"><span class="token punctuation">`</span>user<span class="token punctuation">`</span></span>
<span class="token keyword">FOR EACH ROW</span>
<span class="token keyword">BEGIN</span>
    <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>user_history<span class="token punctuation">`</span></span><span class="token punctuation">(</span>user_id<span class="token punctuation">,</span> operate_type<span class="token punctuation">,</span> operate_time<span class="token punctuation">)</span>
    <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>NEW<span class="token punctuation">.</span>id<span class="token punctuation">,</span> <span class="token string">'add a user'</span><span class="token punctuation">,</span>  <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">END</span> $
<span class="token keyword">DELIMITER</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h3 id="查看触发器"><a href="#查看触发器" class="header-anchor">#</a> 查看触发器</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> TRIGGERS<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="删除触发器"><a href="#删除触发器" class="header-anchor">#</a> 删除触发器</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">TRIGGER</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> trigger_insert_user<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="实际运用"><a href="#实际运用" class="header-anchor">#</a> 实际运用</h2> <h3 id="实际开发中-为什么要替换和避免-sql-中的-in-查询"><a href="#实际开发中-为什么要替换和避免-sql-中的-in-查询" class="header-anchor">#</a> 实际开发中，为什么要替换和避免 SQL 中的 IN 查询？</h3> <p>在实际开发中，替换和避免 SQL 中的 IN 查询有以下几个主要原因：</p> <ol><li><strong>性能问题：</strong> IN 查询在某些情况下可能会导致性能问题。当 IN 中的值过多时，数据库需要逐个匹配，可能会导致全表扫描，造成性能瓶颈。尤其是当 IN 列表中的值很多，数据库需要不断进行多次查询，增加了数据库的负担。</li> <li><strong>查询优化问题：</strong> 数据库优化器在处理 IN 查询时可能会出现问题。不同数据库系统对 IN 查询的优化方式不同，某些情况下可能无法正确优化，导致查询效率下降。</li> <li><strong>内存占用问题：</strong> IN 查询中的值需要在内存中进行匹配，当 IN 列表中的值过多时，会占用大量的内存资源，可能导致内存不足的问题。</li> <li><strong>维护问题：</strong> 使用 IN 查询时，如果需要修改查询条件中的值，需要手动修改 IN 列表中的值，这在维护过程中可能会引入错误。而且，当需要添加或删除值时，也需要相应地修改查询语句。</li></ol> <p>为了避免这些问题，开发中可以考虑以下方法：</p> <ol><li><strong>使用连接查询：</strong> 对于 IN 查询，有时可以使用连接查询（JOIN）来代替。连接查询的优化器通常能更好地处理大量数据，并且可以避免 IN 查询的性能问题。</li> <li><strong>分页查询：</strong> 如果 IN 查询的目的是为了分页显示数据，可以考虑使用分页查询，将数据分批加载，避免一次性查询过多数据。</li> <li><strong>使用索引：</strong> 在 IN 查询中，确保被查询的字段上有适当的索引，以加快查询速度。索引可以帮助数据库快速定位匹配的数据。</li> <li><strong>使用 EXISTS 或子查询：</strong> 对于某些情况，可以使用 EXISTS 或子查询来代替 IN 查询。这种方式在某些情况下可能会更加高效。</li></ol> <p>总之，在开发中应该根据具体的情况来选择是否使用 IN 查询以及如何优化查询性能。合理地使用连接查询、分页查询、索引等方法，可以避免 IN 查询带来的性能和维护问题。</p> <h3 id="为什么可以用-exists-子查询"><a href="#为什么可以用-exists-子查询" class="header-anchor">#</a> 为什么可以用 EXISTS 子查询？</h3> <p><code>EXISTS</code> 是一种用于判断子查询结果是否存在的 SQL 关键字。它通常与主查询一起使用，以确定子查询是否返回了任何行。在某些情况下，使用 <code>EXISTS</code> 可以替代 <code>IN</code> 查询，具有一些优势：</p> <ol><li><strong>性能优化：</strong><code>EXISTS</code> 查询通常可以通过使用索引等优化手段来提高查询性能。数据库优化器可以更好地处理 <code>EXISTS</code> 查询，从而避免了一些 <code>IN</code> 查询可能出现的性能问题。</li> <li><strong>避免重复数据：</strong><code>IN</code> 查询在列表中包含重复值时可能会出现重复的查询结果。而 <code>EXISTS</code> 查询则不会出现这个问题，它只关心是否存在满足条件的记录，而不考虑具体的记录内容。</li> <li><strong>适用范围广：</strong><code>EXISTS</code> 查询更加灵活，可以用于更多不同的查询场景，包括子查询中的聚合操作、连接查询等。</li> <li><strong>内存占用更小：</strong><code>EXISTS</code> 查询不需要加载全部的列表值到内存中进行匹配，因此在处理大量数据时，内存占用相对较小。</li></ol> <p><em>总结</em></p> <ul><li>in 查询是会加载全部列表值，所以性能低</li> <li>exists 只是会判断列表值是否存在</li></ul> <h2 id="参考文献"><a href="#参考文献" class="header-anchor">#</a> 参考文献</h2> <ul><li><a href="https://javaguide.cn/database/sql/sql-syntax-summary.html" target="_blank" rel="noopener noreferrer">SQL语法基础知识总结)<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li> <li><a href="https://juejin.cn/post/6844903790571700231" target="_blank" rel="noopener noreferrer">SQL 语法速成手册<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li> <li><a href="https://www.begtut.com/mysql/mysql-tutorial.html" target="_blank" rel="noopener noreferrer">MySQL 教程<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></div></div> <!----> <div class="page-edit"><!----> <!----> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/126a5a/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">字符集详解</div></a> <a href="/pages/def622/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">SQL必知必会题解</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/126a5a/" class="prev">字符集详解</a></span> <span class="next"><a href="/pages/def622/">SQL必知必会题解</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2023-2023
    <span>霜晨月</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><canvas id="vuepress-canvas-cursor"></canvas></div></div>
    <script src="/assets/js/app.ef25b098.js" defer></script><script src="/assets/js/2.4615a819.js" defer></script><script src="/assets/js/68.163161cc.js" defer></script>
  </body>
</html>
